unit rekap;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ZAbstractTable, ZDataset, DB, ZAbstractRODataset,
  ZAbstractDataset, ZConnection, Setting, Grids;

type
  TForm6 = class(TForm)
    cbKabkot: TComboBox;
    lblKabkot: TLabel;
    lblJtps: TLabel;
    lblTps: TLabel;
    lblJntps: TLabel;
    GroupBox1: TGroupBox;
    GroupBox2: TGroupBox;
    ZConnection1: TZConnection;
    ZQuery1: TZQuery;
    ZTable1: TZTable;
    StringGrid1: TStringGrid;
    StringGrid2: TStringGrid;
    ZTable2: TZTable;
    ZQuery2: TZQuery;
    procedure FormCreate(Sender: TObject);
    procedure cbKabkotChange(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure StringGrid1Click(Sender: TObject);
  private
    procedure AutoSizeCol(Grid: TStringGrid; Column: integer);
    procedure TampilGrid2(idx: Integer);
    procedure resetgrid1;
    procedure resetgrid2;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form6: TForm6;
  skabkot,skec,sdesa:String;
  akec:array of String;

implementation

{$R *.dfm}

procedure TForm6.cbKabkotChange(Sender: TObject);
var
  pilihan,que,kabkot:String;
  urut,ct,ct2,i:integer;
begin
  if (cbKabkot.ItemIndex > 0) then
  begin
    pilihan := cbKabkot.Items[cbKabkot.ItemIndex];
    with ZTable1 do
      begin
        Active := False;
        TableName := 'kabkot';
        Filter := 'TAMPIL = '+QuotedStr(pilihan);
        Filtered := True;
        Active := True;
        First;
        sKabkot := FieldByName('KODE').AsString;
        kabkot:=FieldByName('TAMPIL').AsString;
        //cb2
        Active := False;
        TableName := 'kecamatan';
        Filter := 'KODE_KABKOT = '+QuotedStr(sKabkot);
        Filtered := True;
        Active := True;
        First;
        StringGrid1.RowCount:=RecordCount+1;
        SetLength(akec,RecordCount+1);
        urut:=0;
      end;
    //tampilan keterangan
    lblKabkot.Caption:=kabkot;
    //
    ZQuery1.Active:=false;
     zquery1.SQL.Clear;
     que:='SELECT NAMA FROM penyimpan WHERE NAMA LIKE "pemilih_'+ZTable1.FieldByName('KODE_KABKOT').AsString+'%"';
     ZQuery1.SQL.Text:=que;
     zquery1.Active:=true;
     zquery1.first;
     ct:=0;
     ct2:=0;
     while (not zquery1.Eof) do begin
       zquery2.Active:=false;
       zquery2.SQL.Clear;
       que:='SELECT count(*) FROM '+zquery1.Fields[0].AsString+' WHERE TPS>0';
       zquery2.sql.Text:=que;
       zquery2.Active:=true;
       zquery2.first;
       ct:=ct+zquery2.Fields[0].AsInteger;
     //
       zquery2.Active:=false;
       zquery2.SQL.Clear;
       que:='SELECT count(*) FROM '+zquery1.Fields[0].AsString+' WHERE TPS=0';
       zquery2.sql.Text:=que;
       zquery2.Active:=true;
       zquery2.first;
       ct2:=ct2+zquery2.Fields[0].AsInteger;
      zquery1.Next;
     end;
    lblJtps.Caption:='Jumlah Pemilih Terdaftar dalam TPS: '+IntToStr(ct);
    lblJntps.Caption:='Jumlah Pemilih yang Belum Masuk TPS: '+IntToStr(ct2);
    //
    zquery2.Active:=false;
     zquery2.SQL.Clear;
     que:='SELECT count(*) FROM daftar_tps WHERE KODE_DESA LIKE "'+ZTable1.FieldByName('KODE_KABKOT').AsString+'%"';
     zquery2.sql.Text:=que;
     zquery2.Active:=true;
     zquery2.first;
    lblTps.Caption:='Jumlah TPS: '+zquery2.Fields[0].AsString;
    while (not ZTable1.Eof) do
      begin
        inc(urut);
         //logic
         akec[urut]:=ZTable1.FieldByName('KODE_KECAMATAN').AsString;
         //tampilan grid
         StringGrid1.Cells[0,urut]:=IntToStr(urut);
         StringGrid1.Cells[1,urut]:=ZTable1.FieldByName('TAMPIL').AsString;
         //
         ZQuery1.Active:=false;
         zquery1.SQL.Clear;
         que:='SELECT NAMA FROM penyimpan WHERE NAMA LIKE "pemilih_'+ZTable1.FieldByName('KODE_KECAMATAN').AsString+'%"';
         ZQuery1.SQL.Text:=que;
         zquery1.Active:=true;
         zquery1.first;
         ct:=0;
         ct2:=0;
         while (not zquery1.Eof) do begin
           zquery2.Active:=false;
           zquery2.SQL.Clear;
           que:='SELECT count(*) FROM '+zquery1.Fields[0].AsString+' WHERE TPS>0';
           zquery2.sql.Text:=que;
           zquery2.Active:=true;
           zquery2.first;
           ct:=ct+zquery2.Fields[0].AsInteger;
         //
           zquery2.Active:=false;
           zquery2.SQL.Clear;
           que:='SELECT count(*) FROM '+zquery1.Fields[0].AsString+' WHERE TPS=0';
           zquery2.sql.Text:=que;
           zquery2.Active:=true;
           zquery2.first;
           ct2:=ct2+zquery2.Fields[0].AsInteger;
          zquery1.Next;
         end;
         StringGrid1.Cells[2,urut]:=IntToStr(ct);     //jumlah di tps
         StringGrid1.Cells[4,urut]:=IntToStr(ct2); //jumlah tidak di tps
         //
         zquery2.Active:=false;
         zquery2.SQL.Clear;
         que:='SELECT count(*) FROM daftar_tps WHERE KODE_DESA LIKE "'+ZTable1.FieldByName('KODE_KECAMATAN').AsString+'%"';
         zquery2.sql.Text:=que;
         zquery2.Active:=true;
         zquery2.first;
         StringGrid1.Cells[3,urut]:=zquery2.Fields[0].AsString;   //jumlah tps
         ZTable1.Next;
      end;
      for i := 0 to StringGrid1.ColCount - 1 do
        AutoSizeCol(StringGrid1, i);
  end
    else
  begin
    sKabkot:='';
    ResetGrid1;
  end;
  ResetGrid2;
end;

procedure TForm6.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action := caFree;
end;

procedure TForm6.FormCreate(Sender: TObject);
var
  setting:TSetting_Form;
  i:integer;
begin
  setting:=TSetting_Form.Create;
  ZConnection1.HostName:=setting.host;
  ZConnection1.Protocol:=setting.protocol;
  ZConnection1.Database:=setting.database;
  ZConnection1.User:=setting.user;
  ZConnection1.Password:=setting.password;
  ZConnection1.Connected:=true;
  ztable1.Connection:=ZConnection1;
  ZQuery1.Connection:=ZConnection1;
  ztable2.Connection:=ZConnection1;
  ZQuery2.Connection:=ZConnection1;
  ZConnection1.LoginPrompt:=False;
  ZConnection1.Connected := True;
  //
  ZTable1.Active := False;
  ZTable1.TableName := 'kabkot';
  ZTable1.Active := True;
  ZTable1.First;
  cbKabkot.Clear;
  cbKabkot.Items.Add('Pilih Kab/Kota');
  while (not ZTable1.Eof) do
    begin
      cbKabkot.Items.Add(ZTable1.FieldByName('TAMPIL').AsString);
      ZTable1.Next;
    end;
  sKabkot:='';
  sKec:='';
  sDesa:='';
  lblKabkot.Caption:='Kota/Kab ...';
  lblJtps.Caption:='Jumlah Pemilih Terdaftar dalam TPS: -';
  lblJntps.Caption:='Jumlah Pemilih yang Belum Masuk TPS: -';
  lblTps.Caption:='Jumlah TPS: -';
  StringGrid1.Cells[0,0]:='No';     //jumlah di tps
  StringGrid1.Cells[1,0]:='Nama Kecamatan';     //jumlah di tps
  StringGrid1.Cells[2,0]:='Jumlah Pemilih Terdaftar TPS';     //jumlah di tps
  StringGrid1.Cells[3,0]:='Jumlah TPS';     //jumlah di tps
  StringGrid1.Cells[4,0]:='Jumlah Pemilih Belum Terdaftar TPS';     //jumlah di tps
  //
  StringGrid2.Cells[0,0]:='No';     //jumlah di tps
  StringGrid2.Cells[1,0]:='Nama Kelurahan';     //jumlah di tps
  StringGrid2.Cells[2,0]:='Jumlah Pemilih Terdaftar TPS';     //jumlah di tps
  StringGrid2.Cells[3,0]:='Jumlah TPS';     //jumlah di tps
  StringGrid2.Cells[4,0]:='Jumlah Pemilih Belum Terdaftar TPS';     //jumlah di tps
end;

procedure TForm6.StringGrid1Click(Sender: TObject);
begin
  if (StringGrid1.Row>0) and (StringGrid1.Cells[1,1]<>'') then begin
    TampilGrid2(StringGrid1.Row);
  end
    else
  resetgrid2
end;

procedure TForm6.resetgrid1;
var
  i:integer;
begin
  for i := 1 to StringGrid1.RowCount - 1
    do StringGrid1.Rows[i].Clear;
  StringGrid1.RowCount:=2;
  SetLength(akec,0);
end;

procedure TForm6.resetgrid2;
var
  i:integer;
begin
  for i := 1 to StringGrid2.RowCount - 1
    do StringGrid2.Rows[i].Clear;
  StringGrid2.RowCount:=2;
end;

procedure TForm6.TampilGrid2(idx:Integer);
var
  que,kec:String;
  urut,ct,ct2,i:integer;
begin
  with ZTable1 do
    begin
      Active := False;
      TableName := 'desa';
      Filter := 'KODE_KECAMATAN = '+QuotedStr(akec[idx]);
      Filtered := True;
      Active := True;
      First;
      StringGrid2.RowCount:=RecordCount+1;
      urut:=0;
  end;
  while (not ZTable1.Eof) do
    begin
      inc(urut);
       //tampilan grid
       StringGrid2.Cells[0,urut]:=IntToStr(urut);
       StringGrid2.Cells[1,urut]:=ZTable1.FieldByName('TAMPIL').AsString;
       //
       ZQuery1.Active:=false;
       zquery1.SQL.Clear;
       que:='SELECT NAMA FROM penyimpan WHERE NAMA LIKE "pemilih_'+ZTable1.FieldByName('KODE_DESA').AsString+'%"';
       ZQuery1.SQL.Text:=que;
       zquery1.Active:=true;
       zquery1.first;
       ct:=0;
       ct2:=0;
       if (zquery1.Fields[0].AsString<>'') then begin
         zquery2.Active:=false;
         zquery2.SQL.Clear;
         que:='SELECT count(*) FROM pemilih_'+ZTable1.FieldByName('KODE_DESA').AsString+' WHERE TPS>0';
//         ShowMessage(que);
         zquery2.sql.Text:=que;
         zquery2.Active:=true;
         zquery2.first;
         ct:=ct+zquery2.Fields[0].AsInteger;
       //
         zquery2.Active:=false;
         zquery2.SQL.Clear;
         que:='SELECT count(*) FROM pemilih_'+ZTable1.FieldByName('KODE_DESA').AsString+' WHERE TPS=0';
//         ShowMessage(que);
         zquery2.sql.Text:=que;
         zquery2.Active:=true;
         zquery2.first;
         ct2:=ct2+zquery2.Fields[0].AsInteger;
       end;
       StringGrid2.Cells[2,urut]:=IntToStr(ct);     //jumlah di tps
       StringGrid2.Cells[4,urut]:=IntToStr(ct2); //jumlah tidak di tps
       //
       zquery2.Active:=false;
       zquery2.SQL.Clear;
       que:='SELECT count(*) FROM daftar_tps WHERE KODE_DESA LIKE "'+ZTable1.FieldByName('KODE_DESA').AsString+'%"';
       zquery2.sql.Text:=que;
       zquery2.Active:=true;
       zquery2.first;
       StringGrid2.Cells[3,urut]:=zquery2.Fields[0].AsString;   //jumlah tps
       ZTable1.Next;
    end;
    for i := 0 to StringGrid2.ColCount - 1 do
      AutoSizeCol(StringGrid2, i);
end;

procedure TForm6.AutoSizeCol(Grid: TStringGrid; Column: integer);
var
  i, W, WMax: integer;
begin
  WMax := 0;
  for i := 0 to (Grid.RowCount - 1) do begin
    W := Grid.Canvas.TextWidth(Grid.Cells[Column, i]);
    if W > WMax then
      WMax := W;
  end;
  Grid.ColWidths[Column] := WMax + 5;
end;

end.
